Chapter {seq chapter} {seq chapter sprfile} Series 3/3a and MC Spreadsheet File Format{tc "{seq chapter sprfile} Series 3/3a and MC Spreadsheet File Format"} This document describes the structure of the .spr files that are used by the MC, Series 3 and Series 3a spreadsheets. The description is to a level that allows other software to read and write non-password-protected spreadsheet files. File header{tc "File header" \l 2} The spreadsheet starts with the following header: TEXT fid[16]="SPREADSHEET" packed with trailing zeros UWORD vers=0 UWORD offset=0 UWORD rtvers=0 This header must be supplied exactly as described, otherwise all current versions of the Spreadsheet will refuse to load the file. The only exception is that a password-protected Series 3a spreadsheet file has a file type identifier string (in fid[]) of "SPREADSHEETB". This prevents it from being recognised on Series 3 and MC machines, where password protection of spreadsheet files is not supported. Records{tc "Records" \l 2} The remainder of the file consists of type/length records, each of which has the following structure: UWORD type UWORD len record body the record type the record length len bytes of data The structure of the data in the record body is dependent on the record type. Currently the following types are defined: 1 Formula 2 Cell contents 3 Column width 4 Default column width 5 Status information 6 Display information 7 Named range 8 Print range 9 Database/criteria ranges 10 Table 11 Print setup (MC) 12 Font (MC) 13 Graph (S3) 14 Current graph index (S3) 15 Font palette (S3) 16 Print data (S3) 17 Printer model (S3) 18 Header text (S3) 19 Footer text (S3) 20 Display extras (S3) 21 3a Display extras (S3a) 22 Password (S3a) As indicated in the above table, some of the above types are specific to the MC version and some to the Series 3/3a. In general records can appear in any order, but there are currently three exceptions: • All formula (type 1) records must appear before the cell (type 2) records that access them, and the order in which the formula records appear must be preserved. • The 3a Display extras (type 21) record, when present, should immediately precede the Display information (type 6) record. Otherwise the 3a Display extras record will be ignored. • The Display extras (type 20) record, when present, should immediately precede the 3a Display extras (type 21) record, if one exists. In the absence of a 3a Display extras record, the Display extras record should immediately precede the Display information (type 6) record. Otherwise the Display extras record will be ignored. Some record types may appear many times, and not all record types need be present. Some record types are expected to appear only once in the file. If more than one of each record of such a type is present, the last record will be read and earlier records of the same type will be ignored. The number of times that a particular type of record is expected to occur is indicated in the following descriptions of the record types. Range references{tc "Range references" \l 3} Within any record, a range reference specifies the top left and bottom right cells of the range in column and row units. These references are inclusive so that, for example, the range reference {{0,0},{1,2}} describes the range A1:B3. Formula record (type 1, variable length){tc "Formula record (type 1, variable length)" \l 3} A Spreadsheet file may contain any number of Formula records. Each formula is stored separately from the cell or cells that use it. This allows memory savings to be made by storing only one copy of a commonly used formula. The body of a Formula record is structured as follows: UWORD use UBYTE len UBYTE form[len] usage count formula length the formula The formula is stored in reverse Polish notation (RPN). Each operand is preceded by a byte that identifies its type. Each function or operator is identified by a single byte that follows the operands upon which it acts. Brackets are stored in the formula exactly as they were entered, but are ignored when a formula is evaluated. They are retained only so that the formula can be reproduced and displayed in exactly the same form as it was typed in. The RPN structure is broken for the set of functions that act upon argument lists of variable length (AVG, COUNT, MAX, MIN, STD, SUM and VAR). For these functions a Start token precedes the argument list and an End token follows it. In addition, each operand within the list is preceded by a special token. The tokens used in formulae are as follows: Operators 0x01 Less than 0x02 Less than or equal 0x03 Greater than 0x04 Greater than or equal 0x05 Not equal 0x06 Equal 0x07 Add 0x08 Subtract 0x09 Multiply 0x0a Divide 0x0b Power 0x0c Unary plus 0x0d Unary minus 0x0e Logical NOT 0x0f Logical AND 0x10 Logical OR 0x11 String concatenate Delimiters 0x12 Open bracket 0x13 Close bracket 0x14 Comma 0x15 End of formula Operands 0x16 Double constant (IEEE floating point number) 0x17 Integer constant (WORD) 0x18 Text string (leading-byte-counted string) 0x19 Cell reference (WORD col, WORD row) 0x1a Range reference (WORD tlCol, WORD tlRow, WORD brCol, WORD brRow) Column and/or row references can be either absolute or relative. If the top bit of the WORD is set, the reference is relative, otherwise the reference is absolute. Relative references are treated as a signed offset (ignoring the top bit) from the cell that uses the formula. Note that cell A1 is 0,0. Functions In the following list, 'x' refers to a numeric argument, 'str' a string and 'range' a range reference. 0x1b Err 0x1c False 0x1d Na 0x1e Pi 0x1f Rand 0x20 Now 0x21 True 0x22 Abs(x) 0x23 Acos(x) 0x24 Asin(x) 0x25 At(str) 0x26 Atan(x) 0x27 Cellpointer(x) 0x28 Char(x) 0x29 Code(str) 0x2a Cols(range) 0x2b Cos(x) 0x2c Datevalue(str) 0x2d Day(x) 0x2e Exp(x) 0x2f Hour(x) 0x30 Int(x) 0x31 Iserr(range) 0x32 Isna(range) 0x33 Isnum(range) 0x34 Isstr(range) 0x35 Len(str) 0x36 Ln(x) 0x37 Log(x) 0x38 Lower(str) 0x39 Minute(x) 0x3a Month(x) 0x3b N(range) 0x3c Proper(str) 0x3d Rows(range) 0x3e S(range) 0x3f Second(x) 0x40 Sin(x) 0x41 Sqrt(x) 0x42 Tan(x) 0x43 Timevalue(str) 0x44 Trim(str) 0x45 Upper(str) 0x46 Value(str) 0x47 Year(x) 0x48 Atan2(x,x) 0x49 Cell(x,range) 0x4a Exact(str,str) 0x4b Irr(x,x) 0x4c Left(str,x) 0x4d Mod(x,x) 0x4e Npv(x,x) 0x4f Not used 0x50 Repeat(str,x) 0x51 Right(str,x) 0x52 Round(x,x) 0x53 String(x,x) 0x54 Cterm(x,x) 0x55 Date(x,x) 0x56 Davg(range,x,range) 0x57 Dcount(range,x,range) 0x58 Dmax(range,x,range) 0x59 Dmin(range,x,range) 0x5a Dstd(range,x,range) 0x5b Dsum(range,x,range) 0x5c Dvar(range,x,range) 0x5d Find(str,str,x) 0x5e Fv(x,x,x) 0x5f Hlookup(x,range,x) 0x60 If(x,x,x) 0x61 Index(range,x,x) 0x62 Mid(str,x,x) 0x63 Pmt(x,x,x) 0x64 Pv(x,x,x) 0x65 Rate(x,x,x) 0x66 Sin(x) 0x67 Term(x,x,x) 0x68 Time(x,x,x) 0x69 VLookup(range,x,x) 0x6a Ddb(x,x,x,x) 0x6b Replace(str,x,x,str) 0x6c Syd(x,x,x,x) 0x6d End of avg() 0x6e End of choose() 0x6f End of count() 0x70 End of max() 0x71 End of min() 0x72 End of std() 0x73 End of sum() 0x74 End of var() 0x75 Start of avg() 0x76 Start of choose() 0x77 Start of count() 0x78 Start of max() 0x79 Start of min() 0x7a Start of std() 0x7b Start of sum() 0x7c Start of var() 0x7d Range in avg() 0x7e Range in choose() 0x7f Range in count() 0x80 Range in max() 0x81 Range in min() 0x82 Range in std() 0x83 Range in sum() 0x84 Range in var() 0x85 Cell in avg() 0x86 Cell in choose() 0x87 Cell in count() 0x88 Cell in max() 0x89 Cell in min() 0x8a Cell in std() 0x8b Cell in sum() 0x8c Cell in var() Cell record (type 2, variable length){tc "Cell record (type 2, variable length)" \l 3} A record of this type describes a single spreadsheet cell. There may be any number of these records in the file. The body of a Cell record is structured as follows: UWORD column UWORD row UBYTE flags UBYTE format cell contents Cell co-ordinates (cell A1 is 0,0) See below See below Flags The flags byte contains information about the display alignment of the cell and the cell type. X••••••• •X•••••• ••X••••• •••XX••• •••••XXX used by the natural order sort and should be left as is changed flag, TRUE if (and only if) the cell has changed since the last recalc numeric alignment, 1 left aligned, 0 right aligned text alignment, 00 repeated, 01 left, 10 right, 11 centered cell type, defines the format of the contents data as follows: 000 (0) Blank - contains only the format data shown above and has no contents data 001 (1) Double - contains a floating point constant as a DOUBLE value 010 (2) Text - contains leading-byte-counted text 011 (3) Integer - contains an integer (WORD) constant 101 (5) Double formula - contains a formula that evaluates to a numeric result. Within the file a formula is referenced by the index of the corresponding formula record (this is why the formula recorsd should not be rearranged in an existing file). The contents data is a WORD containing the formula reference, immediately followed by a DOUBLE that contains the current resultant value for the cell 110 (6) Text formula - The cell contains a formula that evaluates to a text result. The contents data is a WORD containing the formula reference, immediately followed by the leading-byte counted text that represents the current resultant value for the cell Format The format byte records whether or not the cell is currently protected and the way in which numeric values should be displayed. X••••••• •XXXXXXX Set if the cell is currently protected Gives the numeric display format as follows: •000XXXX Fixed (XXXX decimal places) •001XXXX Scientific (XXXX decimal places) •010XXXX Currency (XXXX decimal places) •011XXXX Percentage (XXXX decimal places) •100XXXX Comma (XXXX decimal places) •1110000 Bargraph •1110001 General •1110010 Date (Lotus DD-MM-YY) •1110101 Show formulae •1110110 Hidden •1110111 Time (Lotus HH:MM:SS) •1111111 Default For the MC Spreadsheet this is the full extent of the cell record. In files generated on the Series 3/3a there is an extra byte following the cell details described above. This extra byte contains a font style number, in the range 0-3 inclusive, allowing selection of one of the four fonts that are defined by a Font palette (type 15) record. Column width (type 3, length 2){tc "Column width (type 3, length 2)" \l 3} A record of this type is present for each column that is not of the default width. The record body is as follows: UBYTE column UBYTE width the column number (0 for column A) the width of the column (in characters) Default column width (type 4, length 2){tc "Default column width (type 4, length 2)" \l 3} A Spreadsheet file contains only one record of this type. The record body is as follows: UWORD width width (in characters) This record specifies the width of all columns for which there is no specific Column width (type 3) record. Status information (type 5, length 4){tc "Status information (type 5, length 4)" \l 3} A Spreadsheet file contains only one record of this type. It stores assorted information that relates to the spreadsheet as a whole. The body of a Status information record is structured as follows: UWORD flags UBYTE defForm UBYTE defAlign see below the default numeric display format the default text and numeric alignments The numeric display format is one of the values described earlier for a Cell (type 2) record. It is the display format used for all cells whose Cell record specifies the default numeric display format. Clearly, defForm may not itself be set to the default value. The value of defAlign contains the default text and numeric alignments, as described earlier for a Cell (type 2) record. Each newly created cell is set to use the alignments specified by defAlign. Flags The four least significant bits of flags are used for the following purposes: •••••••X ••••••X• •••••X•• ••••X••• set if auto recalc is on set is protection override is on set if cells have been deleted since last recalc set if Table recalc is on Display information (type 6, length 26){tc "Display information (type 6, length 26)" \l 3} Only one Display information record is expected to appear in the file. The body of a record of this type contains the current state of the display for the spreadsheet. It is structured as follows: UWORD titleTlCol UWORD titleTlRow UWORD titleBrCol UWORD titleBrRow UWORD topCol UWORD topRow UWORD selTlCol UWORD selTlRow UWORD selBrCol UWORD selBrCol UWORD cursorCol UWORD cursorRow UBYTE lines UBYTE hideZeros range for the titles select range position of cursor TRUE if grid lines are to be displayed TRUE if zero values are hidden Named range (type 7, length 26){tc "Named range (type 7, length 26)" \l 3} Each Named range record specifies a range or cell to be associated with a name. There may be any number of such records in a file. The body of the record is structured as follows: TEXT name[16] UWORD range_left_column UWORD range_top_row UWORD range_right_column UWORD range_bottom_row UWORD type zero terminated text string the range associated with name reference type The value of type is 25 (0x19) for a cell reference and 26 (0x1a) for a range reference. These values are chosen to match the reference tokens used in formulae. Print range (type 8, length 8){tc "Print range (type 8, length 8)" \l 3} A record of this type specifies a range to be offered for selective printing. There may be any number of these records in the file. The body of a Print range record is structured as follows: UWORD range_left_column UWORD range_top_row UWORD range_right_column UWORD range_bottom_row the range Database, criterion settings (type 9, length 16){tc "Database, criterion settings (type 9, length 16)" \l 3} A record of this type specifies the criterion and database ranges to be used by the database commands. A Spreadsheet file is expected to contain zero or one Database/criterion records. The body of the record is structured as follows: UWORD crit_left_col UWORD crit_top_row UWORD crit_right_col UWORD crit_bottom_row UWORD dbase_left_col UWORD dbase_top_row UWORD dbase_right_col UWORD dbase_bottom_row criterion range database range Table information (type 10, length 16){tc "Table information (type 10, length 16)" \l 3} A Spreadsheet file is expexted to contain zero or one Table information records. The body of the record is structured as follows: UWORD range_left_col UWORD range_top_row UWORD range_right_col UWORD range_bottom_row UWORD input1_col UWORD input1_row UWORD input2_col UWORD input2_row table range input cell 1 input cell 2 For table 2 both input cells are valid. For table 1 input2_col must be set to 0xffff (65535). Print setup (type 11, length 2){tc "Print setup (type 11, length 2)" \l 3} The Print setup record only appears in an MC Spreadsheet file, and only only one such record is expected to appear in the file. The body of the record is structured as follows: UWORD type Allowed values for type are: ••••••••X •••••••X• ••••••X•• •••••X••• if TRUE show values, else show formulae if TRUE, show hidden cells if TRUE, show column seperators if TRUE, show headers Font (type 12, length 18){tc "Font (type 12, length 18)" \l 3} The Font record only appears in an MC Spreadsheet file, and only only one such record is expected to appear in the file. The body of the record is structured as follows: UWORD type TEXT name[16] font name The value of type specifies the associated style, as indicated below: •••••••X ••••X••• bold double height Graph (type 13, variable length){tc "Graph (type 13, variable length)" \l 3} A Graph record only appears in a Series 3/3a Spreadsheet file, and there may be any number of records of this type in the file. Each record defines a displayable graph. The body of the record is structured as follows: TEXT name[16] UWORD A_range[4] UWORD B_range[4] UWORD C_range[4] UWORD D_range[4] UWORD E_range[4] UWORD F_range[4] UWORD X_range[4] UWORD A_labels[4] UWORD B_labels[4] UWORD C_labels[4] UWORD D_labels[4] UWORD E_labels[4] UWORD F_labels[4] UBYTE fmts[6] UBYTE aligns[6] UBYTE xAxisScaling UBYTE xAxisFormat DOUBLE xAxisLowerLimit DOUBLE xAxisUpperLimit UBYTE yAxisScaling UBYTE yAxisFormat DOUBLE yAxisLowerLimit DOUBLE yAxisUpperLimit UBYTE graphType UBYTE gridFlags UBYTE colour UBYTE rangeFlags UBYTE labelFlags UBYTE otherFlags WORD skip ten strings zero terminated name for the graph a data range left_col, top_row, right_col, bottom_row range containing labels for data set A There are ten zero terminated strings packed consecutively, following skip. These strings are, in order: First line of title - max length 40 Second line of title - max len 40 Title for x axis - max len 40 Title for y axis - max len 40 Legend for A range - max len 20 . . . Legend for F range - max len 20 Current graph index (type 14, length 2){tc "Current graph index (type 14, length 2)" \l 3} A record of this type only appears in a Series 3/3a Spreadsheet file, and only one Current graph index record is expected to appear in the file. The body of the record is structured as follows: UWORD curGraph It contains the index of the graph that is currently selected, as set by the 'Use graph' menu item. Graphs are indexed in the order that the corresponding Graph (type 13) records appear in the file, with an index of 0 referring to the first graph. Font palette (type 15, length 24){tc "Font palette (type 15, length 24)" \l 3} A record of this type only appears in a Series 3/3a Spreadsheet file, and only one Font palette record is expected to appear in the file. The body of the record is structured as follows: SCRLAY_FONT font1 SCRLAY_FONT font2 SCRLAY_FONT font3 SCRLAY_FONT font4 Each SCRLAY_FONT structure determines one of the four selectable fonts and their corresponding styles (bold, italic etc.). The SCRLAY_FONT struct is defined in scrlay.g as: typedef struct { UWORD fid; window server font id UWORD style; font style (eg bold) UWORD height; height of printer font in decipoints } SCRLAY_FONT; Print data (type 16, length 58){tc "Print data (type 16, length 58)" \l 3} A Print data record only appears in a Series 3/3a Spreadsheet file, and only one record of this type is expected to appear in the file. This record contains a PRINTER_PARAMS struct that supplies information required to format the document for the printer. It includes, amongst other items, descriptions of the page size and margins, the page numbering style, header and footer position and alignment. The detailed description of this record is outside the scope of this document. For some further details of this record, and of the other printer-related records (types 17, 18 and 19), see Saving and restoring print context from file in the Printing chapter of the Object Oriented Programming Guide. Printer model (type 17, variable length){tc "Printer model (type 17, variable length)" \l 3} A Printer model record only appears in a Series 3/3a Spreadsheet file, and only one record of this type is expected to appear in the file. The body of the record is structured as follows: UBYTE modelIndex TEXT ztsDriver The data consists of a zero terminated string, containing the full path name of the current printer driver file, preceded by a one byte index to the printer model within the file. The default value is: 0 "ROM::BJ.WDR" Header text (type 18, variable length){tc "Header text (type 18, variable length)" \l 3} This record type only appears in a Series 3/3a Spreadsheet file, and only one Header text record is expected to appear in the file. The body of the record is structured as follows: TEXT zts The zero terminated string contains the text used for the page header when printing. Footer text (type 19, variable length){tc "Footer text (type 19, variable length)" \l 3} This record type only appears in a Series 3/3a Spreadsheet file, and only one Footer text record is expected to appear in the file. The body of the record is structured as follows: TEXT zts The zero terminated string contains the text used for the page footer when printing. Display extras (type 20, length 2){tc "Display extras (type 20, length 2)" \l 3} A Display extras record only appears in a Series 3/3a Spreadsheet file. A record of this type is ignored unless it immediately precedes either the 3a Display extras (type 21) record, if it exists, or the Display information (type 6) record. The body of the record is structured as follows: UWORD flags Only two bits of flags are used: •••••••X ••••••X• set if grid labels are shown set if small font is used (this is over-ridden if a 3a Display extras (type 21) record is present. 3a Display extras (type 21, length 4){tc "3a Display extras (type 21, length 4)" \l 3} A 3a Display extras record only appears in a Series 3a Spreadsheet file. A record of this type is ignored unless it immediately precedes the Display information (type 6) record. The body of the record is structured as follows: UBYTE font UBYTE statwin WORD spare font used status window for future expansion Font This takes one of the values: 255 8 9 10 11 smallest font available Swiss 8 pixel font Swiss 11 pixel font Swiss 13 pixel font Swiss 16 pixel font Statwin This takes one of the values: 0 1 2 No status window Small status window Large status window Password (type 22, length 18){tc "Password (type 22, length 18)" \l 3} This record is present only if the spreadsheet is password-protected. Its content is used as part of the spreadsheet encryption/decryption process. Additional system information {seq chapter sprfile|0} Series 3/3a and MC Spreadsheet File Format {page|12} {page|11} {page|1}